IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageCopy]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageCopy]
GO
create procedure [dbo].[cms_sp_PageCopy]
( 
@PageId int,
@CreatedBy int,
@DateCreated datetime,
@PageTypeParameter nvarchar(150),
@ContentTypeParameter nvarchar(150),
@PageContentTypeParameter nvarchar(150),
@PageControlTypeParameter nvarchar(150)
)
as
declare @NewPageId int
declare @PageNodeId hierarchyid
declare @ParentPageNodeId hierarchyid
declare @PageTypeKey int
declare @SiteId int
declare @SiteGroupId int
declare @PageUrl varchar(250)
declare @PageUrlBase varchar(250)

select 
	@ParentPageNodeId = CASE WHEN PageNodeId.GetLevel()>0 THEN PageNodeId.GetAncestor(1) ELSE hierarchyid::GetRoot() END,
	@PageTypeKey = PageTypeKey,
	@PageUrlBase = PageUrl + '-copy',
	@SiteId = SiteId,
	@SiteGroupId = SiteGroupId
from dbo.Pages
where PageId = @PageId

-- PageNodeId
select @PageNodeId = max(PageNodeId) from dbo.Pages
	where PageNodeId.GetAncestor(1) = @ParentPageNodeId

set @PageNodeId = @ParentPageNodeId.GetDescendant(@PageNodeId, null)

-- URL
declare @CopyIndex int = 1
set @PageUrl = @PageUrlBase

while (select COUNT(*) 
        from [dbo].[Pages] inner join [dbo].[EntityRevisions]
	    on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter
        where (([SiteGroupId] is null and [SiteId] is null) or 
   			([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
			([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId)) and 
			EntityRevisions.IsLatestRevision = 1 and
			EntityRevisions.IsDeleted = 0 and
			Pages.PageUrl = @PageUrl)>0
begin
   set @PageUrl = @PageUrlBase + CONVERT(varchar,@CopyIndex)
   set @CopyIndex = @CopyIndex + 1
end

insert into dbo.Pages
(
SiteGroupId,
SiteId,
SystemLanguageId,
TemplateId,
PageNodeId,
PublicPageId,
PageTypeKey,
PageBehaviourTypeKey,
PageName,
PageUrl,
IsVisible,
UseFileCache,
PageTitle,
PageTitleOverride,
PageDescription,
PageKeywords,
CreatedBy,
DateCreated,
PageKey
)
select 
SiteGroupId,
SiteId,
SystemLanguageId,
TemplateId,
@PageNodeId,
0,
PageTypeKey,
PageBehaviourTypeKey,
PageName + ' copy',
@PageUrl,
IsVisible,
UseFileCache,
PageTitle,
PageTitleOverride,
PageDescription,
PageKeywords,
@CreatedBy,
@DateCreated,
PageKey
from dbo.Pages
where PageId=@PageId
set @NewPageId = SCOPE_IDENTITY()

update dbo.Pages
	set PublicPageId = @NewPageId
where PageId = @NewPageId

insert into dbo.EntityRevisions
(
EntityId,
PublicEntityId,
EntityTypeParameter,
WorkflowId,
IsLatestRevision,
IsLatestCompletedRevision,
IsDeleted,
IsPublished
)
values
(
@NewPageId,
@NewPageId,
@PageTypeParameter,
null,
1,
0,
0,
0
)
-- copy all page's properties
declare @EntityMetaFieldLinkId int
declare @EntityMetaValue nvarchar(max)
declare page_property_cursor cursor for 
select
	EntityMetas.EntityMetaFieldLinkId,
	EntityMetas.EntityMetaValue
from dbo.EntityMetas
inner join dbo.EntityMetaFieldLinks
	on EntityMetaFieldLinks.EntityMetaFieldLinkId = EntityMetas.EntityMetaFieldLinkId and
		EntityMetaFieldLinks.EntityTypeParameter = @PageTypeParameter
where EntityId = @PageId
open page_property_cursor;
fetch next from page_property_cursor 
	into @EntityMetaFieldLinkId, @EntityMetaValue
while @@fetch_status = 0
begin
	insert into dbo.EntityMetas
	(
	EntityMetaFieldLinkId,
	EntityId,
	EntityMetaValue
	)
	values
	(
	@EntityMetaFieldLinkId,
	@NewPageId,
	@EntityMetaValue
	)
fetch next from page_property_cursor 
	into @EntityMetaFieldLinkId, @EntityMetaValue
end
close page_property_cursor
deallocate page_property_cursor
-- copy all page's controls
declare @PageControlId int
declare @TemplateRegionId int
declare @ModuleId int
declare @PageControlTypeKey int
declare @PageControlContentId int
declare @PageControlViewValue xml
declare @PageControlModelValue xml
declare @PageControlOrder int
declare @ContentId int
declare @ContentTypeId int
select 
	@ContentTypeId = ContentTypeId 
from [dbo].[ContentTypes]
where ContentTypeName = @PageContentTypeParameter
declare page_control_cursor cursor for 
select 
	PageControlId,	
	TemplateRegionId,
	ModuleId,
	PageControlTypeKey,
	PageControlContentId,
	PageControlViewValue,
	PageControlModelValue,
	PageControlOrder
from dbo.PageControls
inner join dbo.EntityRevisions
	on EntityRevisions.EntityId = PageControls.PageControlId and 
	EntityRevisions.EntityTypeParameter = @PageControlTypeParameter and
	EntityRevisions.IsLatestRevision = 1
where PageId = @PageId
open page_control_cursor;
fetch next from page_control_cursor 
	into 
		@PageControlId,		
		@TemplateRegionId,
		@ModuleId,
		@PageControlTypeKey,
		@PageControlContentId,
		@PageControlViewValue,
		@PageControlModelValue,
		@PageControlOrder
while @@fetch_status = 0
begin
	if @PageControlTypeKey = 1 -- page content
	begin
		insert into dbo.Contents
		(
		SiteId,
		SiteGroupId,
		SystemLanguageId,
		ContentTypeId,
		PublicContentId,
		ContentTitle,
		ContentBody,
		CreatedBy,
		DateCreated
		)
		select 
			@SiteId,
			@SiteGroupId,
			SystemLanguageId,
			@ContentTypeId,
			0,
			ContentTitle,
			ContentBody,
			@CreatedBy,
			@DateCreated
		from dbo.Contents
		where ContentId = @PageControlContentId
		set @ContentId = SCOPE_IDENTITY()
		update dbo.Contents
			set PublicContentId = @ContentId
		where ContentId = @ContentId
		insert into dbo.EntityRevisions
		(
		EntityId,
		PublicEntityId,
		EntityTypeParameter,
		WorkflowId,
		IsLatestRevision,
		IsLatestCompletedRevision,
		IsDeleted,
		IsPublished
		)
		values
		(
		@ContentId,
		@ContentId,
		@ContentTypeParameter,
		null,
		1,
		0,
		0,
		0
		)
	end
	insert into dbo.PageControls
	(
	PageId,
	TemplateRegionId,
	ModuleId,
	PageControlContentId,
	PageControlTypeKey,
	PageControlViewValue,
	PageControlModelValue,
	PageControlOrder,
	CreatedBy,
	DateCreated
	)
	values
	( 
	@NewPageId,
	@TemplateRegionId,
	@ModuleId,
	(case @PageControlTypeKey
		when 1 then @ContentId
		else @PageControlContentId
	end),
	@PageControlTypeKey,
	@PageControlViewValue,
	@PageControlModelValue,
	@PageControlOrder,
	@CreatedBy,
	@DateCreated
	)
	set @PageControlId = SCOPE_IDENTITY()
	insert into dbo.EntityRevisions
	(
	EntityId,
	PublicEntityId,
	EntityTypeParameter,
	WorkflowId,
	IsLatestRevision,
	IsLatestCompletedRevision,
	IsDeleted,
	IsPublished
	)
	values
	(
	@PageControlId,
	@PageControlId,
	@PageControlTypeParameter,
	null,
	1,
	0,
	0,
	0
	)
fetch next from page_control_cursor 
	into 
		@PageControlId,		
		@TemplateRegionId,
		@ModuleId,
		@PageControlTypeKey,
		@PageControlContentId,
		@PageControlViewValue,
		@PageControlModelValue,
		@PageControlOrder
end
close page_control_cursor
deallocate page_control_cursor

exec cms_sp_PageSelectOne @PageId=@NewPageId, @PublicPageId=null, @SiteGroupId=@SiteGroupId, @SiteId=@SiteId, @SystemLanguageId=null, @PageTypeParameter=@PageTypeParameter, @PageControlTypeParameter=@PageControlTypeParameter, @IsInAdministration=1
GO